//this file pulls in and builds the unemployment rates/labor market file 
	//these data are pulled from Local Area Unemployment Statistics from the BLS

global direc "[YOUR PATH HERE]"
global raw_data "${direc}/data/raw"


import excel "${raw_data}/annual_labormarket_bystate.xlsx", firstrow clear

rename Annual* _*_
	drop seasonality

replace variable = "unemp_rate" if variable == "unemployment rate"
replace variable = "labforce" if variable == "labor force"
replace variable = "emp_pop_ratio" if variable == "employment-pop ratio"
replace variable = "lfp_rate" if variable == "labor force participation rate"
replace variable = "civ_pop" if variable == "civilian non-inst population"
	
reshape wide _*_ , i(state) j(variable, string)
	rename _*_unemp_rate       unemp_rate_*
	rename _*_labforce         labforce_*
	rename _*_emp_pop_ratio    emp_pop_ratio_*
	rename _*_lfp_rate         lfp_rate_*
	rename _*_civ_pop          civ_pop_*
	rename _*_employment       employment_*
	rename _*_unemployed       unemployed_*

reshape long unemp_rate_ labforce_ emp_pop_ratio_ lfp_rate_ civ_pop_ employment_ unemployed_, i(state) j(year)

gen statefips = 01 if state == "alabama"
replace statefips = 02 if state == "alaska" 
replace statefips = 04 if state == "arizona"
replace statefips = 05 if state == "arkansas"
replace statefips = 06 if state == "california"
replace statefips = 08 if state == "colorado"
replace statefips = 09 if state == "connecticut"
replace statefips = 10 if state == "delaware"
replace statefips = 11 if state == "dc"
replace statefips = 12 if state == "florida"
replace statefips = 13 if state == "georgia"
replace statefips = 15 if state == "hawaii"
replace statefips = 16 if state == "idaho"
replace statefips = 17 if state == "illinois"
replace statefips = 18 if state == "indiana"
replace statefips = 19 if state == "iowa"
replace statefips = 20 if state == "kansas"
replace statefips = 21 if state == "kentucky"
replace statefips = 22 if state == "louisiana"
replace statefips = 23 if state == "maine"
replace statefips = 24 if state == "maryland"
replace statefips = 25 if state == "massachusetts"
replace statefips = 26 if state == "michigan"
replace statefips = 27 if state == "minnesota"
replace statefips = 28 if state == "mississippi"
replace statefips = 29 if state == "missouri"
replace statefips = 30 if state == "montana"
replace statefips = 31 if state == "nebraska"
replace statefips = 32 if state == "nevada"
replace statefips = 33 if state == "new hampshire"
replace statefips = 34 if state == "new jersey"
replace statefips = 35 if state == "new mexico"
replace statefips = 36 if state == "new york"
replace statefips = 37 if state == "north carolina"
replace statefips = 38 if state == "north dakota"
replace statefips = 39 if state == "ohio"
replace statefips = 40 if state == "oklahoma"
replace statefips = 41 if state == "oregon"
replace statefips = 42 if state == "pennsylvania"
replace statefips = 44 if state == "rhode island"
replace statefips = 45 if state == "south carolina"
replace statefips = 46 if state == "south dakota"
replace statefips = 47 if state == "tennessee"
replace statefips = 48 if state == "texas"
replace statefips = 49 if state == "utah"
replace statefips = 50 if state == "vermont"
replace statefips = 51 if state == "virginia"
replace statefips = 53 if state == "washington"
replace statefips = 54 if state == "west virginia"
replace statefips = 55 if state == "wisconsin"
replace statefips = 56 if state == "wyoming"

save "${direc}/data/clean_controls_labormarket",  replace

